

import java.io.*;
import java.sql.*;
import java.util.Properties;
import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.HSSFColor;
import java.util.Date;

public class db2xl {
	static FileOutputStream fileOut;
	static HSSFWorkbook hwb;
	static HSSFSheet sheet;
	static int i = 0;
	public static void main(String[] args) {
		try {
			String filename = args[0].substring(0, args[0].lastIndexOf(File.separator))+File.separator+"SQl"+new Date().toString().replace(":","-")+".xls";
			
			System.out.println(filename);
			fileOut= new FileOutputStream(filename);
			 hwb = new HSSFWorkbook();
			 sheet = hwb.createSheet("result");
			/*
			 * rowhead.createCell((short) 0).setCellValue("SNo");
			 * rowhead.createCell((short) 1).setCellValue("Name");
			 * rowhead.createCell((short) 2).setCellValue("Address");
			 * rowhead.createCell((short) 3).setCellValue("Contact No");
			 * rowhead.createCell((short) 4).setCellValue("E-mail");
			 */
			//Class.forName("oracle.jdbc.driver.OracleDriver");
			// Class.forName("com.mysql.jdbc.Driver");
			//String url = "jdbc:oracle:thin:@//192.168.1.23:1521/orcl";
			 String dbConfigFile = args[0];
			    String sqlFile = args[1];
			    String contId = args[2];
			Connection con ;
			 con = getConnection(dbConfigFile);
			Statement st = con.createStatement();
			
			Vector<String> sqls = getSQLsFromFile(sqlFile);
			//String sql = "Select * from database d,H_database";
			ResultSet rs;
			for (String sql : sqls)
		      {
		        sql = sql.replace("?", contId);
		        rs =executeSQL(con, sql);
		        System.out.println(sql);
		        createxl(rs,sql);
		      }
			hwb.write(fileOut);
			fileOut.close();
			
	}
		catch (Exception ex) {
			System.out.println(ex);
			
			ex.getMessage();

		}	
		
	}
		public static ResultSet executeSQL(Connection con, String sql) throws SQLException
			  {
			    ResultSet rs = con.createStatement().executeQuery(sql);

			    return rs;
			  }
			  
		
		public static void createxl( ResultSet rs, String sql) throws IOException, SQLException
		{
			
			String tempSql = sql.toUpperCase();
			String[] tep = tempSql.split("FROM");
			String[] te = tep[1].split("WHERE");
			

			// HSSFRow rowhead= sheet.createRow((short)0);
			// pw.println(te[0]);
			// String tableName = tempSql.substring(tbIdStart+5, tbIdEnd);
			
			HSSFRow rowhead1 = sheet.createRow((short) i++);
			//
			HSSFCell cell2B;
			HSSFCellStyle cellStyle = hwb.createCellStyle();
			cellStyle = hwb.createCellStyle();
			cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
			cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			cellStyle.setBorderTop((short) 2); // single line border
			cellStyle.setBorderBottom((short) 2); // single line border
			cellStyle.setBorderLeft((short) 2);
			cellStyle.setBorderRight((short) 2);
			HSSFFont font = hwb.createFont();
			font.setColor(HSSFColor.BLACK.index);
			font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
			cellStyle.setFont(font);
			HSSFCellStyle cellStyle1 = hwb.createCellStyle();
			cellStyle1 = hwb.createCellStyle();
			cellStyle1.setFillForegroundColor(HSSFColor.CORAL.index);
			cellStyle1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			cellStyle1.setBorderTop((short) 1); // single line border
			cellStyle1.setBorderBottom((short) 1); // single line border
			cellStyle1.setBorderLeft((short) 1);
			cellStyle1.setBorderRight((short) 1);
			sheet.setMargin((short) 2, 1.0);
			ResultSetMetaData md = rs.getMetaData();
			// cell2B=rowhead.createCell((short) 1);
			// cell2B.setCellValue(te[0]);
			// cell2B.setCellStyle(cellStyle);
			int count = md.getColumnCount();
			for (int i1 = 1; i1 <= count; i1++) {
				sheet.autoSizeColumn(i1 - 1);
				cell2B = rowhead1.createCell((short) i1 - 1);
				cell2B.setCellValue(md.getColumnLabel(i1));
				cell2B.setCellStyle(cellStyle1);
				// pw.println(md.getColumnLabel(i));
			}
			
			String name;
			while (rs.next()) {
				HSSFRow row = sheet.createRow((short) i);
				for (int i1 = 1; i1 <= count; i1++) {
					sheet.autoSizeColumn(i1);
					if (rs.getString(i1) == null) {
						name = "";
					} else {
						name = rs.getString(i1).toString();
					}

					cell2B = row.createCell((short) i1 - 1);
					cell2B.setCellValue(name);
					cell2B.setCellStyle(cellStyle);
				}

				i++;
			}
			
			i++;i++;
			
			System.out.println("Your excel file has been generated!");

		} 

	public static Connection getConnection(String dbConfigFile) throws Exception
	  {
	    Class.forName("oracle.jdbc.driver.OracleDriver");
		//  Class.forName("com.ibm.db2.jcc.DB2Driver");
		Properties prop = new Properties();
		prop.load(new FileInputStream(dbConfigFile));
	    String user = prop.getProperty("UserName");
	    String pass = prop.getProperty("Password");
	    String url = prop.getProperty("DBURL");
		
	    Connection con = 
	      DriverManager.getConnection(url, user, pass);

	    return con;
	  }

	public static Vector<String> getSQLsFromFile(String fileName) throws Exception {

		Vector<String> sqls = new Vector<String>();
		DataInputStream in = null;
		try {
			// Open the file that is the first
			// command line parameter
			FileInputStream fstream = new FileInputStream(fileName);
			// Get the object of DataInputStream
			in = new DataInputStream(fstream);
			BufferedReader br = new BufferedReader(new InputStreamReader(in));
			String strLine;
			// Read File Line By Line
			while ((strLine = br.readLine()) != null) {
				// Print the content on the console
				if (strLine.startsWith("#") || strLine.trim().equals("")) {
				} else
					sqls.add(strLine);
			}

		} finally {
			try {
				if (in != null) {
					in.close();
				}
			} catch (Exception e) {
			}
		}
		return sqls;
	}

}